﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Common;
using CNCSoft.ErpApps.TMS.myclass;

namespace CNCSoft.ErpApps.TMS
{
    public partial class frmDangKyMonThiChoLop : Form
    {
        private SqlConnection conn = null;
        private DataSet ds = null;
        private string companyID = null;
        private CommonClass mine;
        private bool sign = false;
        private bool sign2 = false;
        private bool signTableNgayThiCuaLop = false;
        private IFormatProvider culture;

        public frmDangKyMonThiChoLop()
        {
            InitializeComponent();
        }

        private void frmDangKyMonThiChoLop_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
            ds = new DataSet();
            companyID = GlobalVariable.WSInfo.CompanyID;
            mine = new CommonClass();
            culture = new System.Globalization.CultureInfo("fr-FR", true);
            LoadControl();
        }

        private void searchLookUpEditKhoaHoc_EditValueChanged(object sender, EventArgs e)
        {
            try
            {
                string makhoahoc = searchLookUpEditKhoaHoc.EditValue.ToString();
                LoadMonHocByKhoaHoc(makhoahoc);
                LoadNgayThiByClass(makhoahoc);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void cmbMonHoc_SelectedIndexChanged(object sender, EventArgs e)
        {
            string mamonhoc = null;
            if (cmbMonHoc.SelectedItem != null)
            {
                mamonhoc = cmbMonHoc.SelectedValue.ToString();
            }
            else
            {
                mamonhoc = "";
            }
            LoadMonThiByMonHoc(mamonhoc);
        }

        private void btnDangKy_Click(object sender, EventArgs e)
        {
            DangKy();
        }

        #region coding
        private void LoadControl()
        {
            try
            {
                string sql =
                @"SELECT dmkh.MaKhoaHoc, dmkh.TenKhoaHoc
                    FROM DM_KHOAHOC dmkh
                    WHERE dmkh.MaDonVi = '" + companyID + "'";
                mine.ExecuteDataAdapter(sql, conn, ds, "TableLop", "MaKhoaHoc", "TenKhoaHoc", searchLookUpEditKhoaHoc);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadMonHocByKhoaHoc(string makhoahoc)
        {
            try
            {
                if (sign)
                {
                    ds.Tables["DM_MONHOC"].Rows.Clear();
                }
                string sqlSubject =
                    @"SELECT DISTINCT dmmh.MaMonHoc, dmmh.TenMonHoc, dmkh.TenKhoaHoc
                        FROM DM_KHOAHOC dmkh 
                        INNER JOIN Invoice_KhoaHoc invkh
                        ON dmkh.MaKhoaHoc = invkh.MaKhoaHoc
                        INNER JOIN Invoice_KhoaHoc_QTHT qtht
                        ON invkh.ID = qtht.MaHocTap
                        INNER JOIN DM_MONHOC dmmh
                        ON dmmh.MaMonHoc = qtht.MaMonHoc
                        WHERE invkh.MaKhoaHoc = '" + makhoahoc + "' AND dmkh.MaDonVi = '" + companyID + "'";
                mine.ExecuteDataAdapter(sqlSubject, conn, ds, "DM_MONHOC", "MaMonHoc", "TenMonHoc", cmbMonHoc);
                sign = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadMonThiByMonHoc(string mamonhoc)
        {
            try
            {
                string sql =
                    @"SELECT 
                        dmmt.MaMonThi as N'Mã môn thi',
                        dmmt.TenMonThi as N'Tên môn thi',
                        dmmh.TenMonHoc as N'Môn học',
                        dmmt.ThangDiem as N'Thang điểm'
                        FROM 
                        DM_MONTHI dmmt INNER JOIN DM_MONHOC dmmh
                        ON
                        dmmt.MaMonHoc = dmmh.MaMonHoc
                        WHERE dmmt.MaMonHoc = '" + mamonhoc + "' AND dmmh.MaDonVi = '" + companyID + "'";
                if (sign2)
                {
                    ds.Tables["TableMonThi"].Rows.Clear();
                }
                mine.ExecDataAdapter(sql, conn, ds, "TableMonThi", gridControlMonThi);
                sign2 = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private DataTable GetMaHocTapByKhoaHoc(string makhoahoc)
        {
            try
            {
                string sql =
                    @"SELECT ID FROM Invoice_KhoaHoc
                        WHERE MaKhoaHoc = '" + makhoahoc + "'";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableHocTap");
                return ds.Tables["TableHocTap"];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        private void LoadNgayThiByClass(string makhoahoc)
        {
            try
            {
                if (signTableNgayThiCuaLop)
                {
                    ds.Tables["TableNgayThiCuaLop"].Rows.Clear();
                }
                string sql =
                    @"SELECT DISTINCT
                        dmkh.TenKhoaHoc AS N'Tên lớp',
                        dmmt.TenMonThi AS N'Tên môn thi',
                        CONVERT(VARCHAR, kqht.NgayThi, 103) AS N'Ngày thi'
                        FROM DM_KHOAHOC dmkh
                        LEFT OUTER JOIN Invoice_KhoaHoc invkh ON dmkh.MaKhoaHoc = invkh.MaKhoaHoc
                        LEFT OUTER JOIN Invoice_KhoaHoc_KQHT kqht ON kqht.MaHocTap = invkh.ID
                        INNER JOIN DM_MONTHI dmmt ON dmmt.MaMonThi = kqht.MaMonThi
                        WHERE dmkh.MaKhoaHoc = '" + makhoahoc +"'";
                mine.ExecDataAdapter(sql, conn, ds, "TableNgayThiCuaLop", gridControlNgayThiCuaLop);
                signTableNgayThiCuaLop = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void DangKy()
        {
            try 
	        {
                if (dateEditNgayThi.Text != "")
                {
                    int i = 0;
                    DataTable dt = GetMaHocTapByKhoaHoc(searchLookUpEditKhoaHoc.EditValue.ToString());
                    DateTime ngaythi = DateTime.Parse(dateEditNgayThi.Text, culture, System.Globalization.DateTimeStyles.AssumeLocal);
                    foreach (DataRow dr in dt.Rows)
                    {
                        string strField = null;
                        string mahoctap = dr[0].ToString();
                        foreach (string mamonthi in mine.ChoosedValue(gridViewMonThi, strField))
                        {
                            string sql =
                                @"INSERT INTO Invoice_KhoaHoc_KQHT(MaHocTap, MaMonThi, NgayThi)
                                VALUES(@mahoctap, @mamonthi, @ngaythi)";
                            SqlCommand cmd = new SqlCommand(sql, conn);
                            cmd.Parameters.AddWithValue("@mahoctap", mahoctap);
                            cmd.Parameters.AddWithValue("@mamonthi", mamonthi);
                            cmd.Parameters.AddWithValue("@ngaythi", ngaythi);
                            try
                            {
                                conn.Open();
                                i = cmd.ExecuteNonQuery();
                            }
                            finally
                            {
                                if (conn != null)
                                    conn.Close();
                            }
                        }
                    }
                    if (i > 0)
                    {
                        MessageBox.Show("Đăng ký thành công");
                        LoadNgayThiByClass(searchLookUpEditKhoaHoc.EditValue.ToString());
                    }
                    else
                        MessageBox.Show("Thất bại");
                }
                else
                {
                    MessageBox.Show("Bạn chưa chọn ngày thi");
                }
	        }
	        catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }            
        }
        #endregion
    }
}
